package kr.hkit.powerjava.chapter26.Young;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

public class DepartmentDAO implements DaoInterface<Department> {

	private Connection connection;

	public DepartmentDAO(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void insertItem(Department item) throws SQLException {
		String sql = "insert into department values(?,?,?)";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		pstmt.setString(2, item.getDeptName());
		pstmt.setInt(3, item.getFloor());

		/*
		System.out.println(pstmt); //확인차 해봄 
		int res = pstmt.executeUpdate();
		System.out.println(res);*/

		pstmt.executeUpdate();
		DbUtil.close(pstmt);
		// 클로즈필요

	}

	@Override
	public void deleteItem(Department item) throws SQLException {
		String sql = "delete from department where deptno =? and deptname=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());	
		pstmt.setString(2, item.getDeptName());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);

	}

	@Override
	public void updateItem(Department item) throws SQLException {
		String sql = "update department set deptname=?, floor=? where deptno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptName());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);

	}

	@Override
	public Department searchItem(Department item) throws SQLException {
		Department department = null;
		String sql = "select deptno, deptname, floor from department where deptno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		ResultSet rs = pstmt.executeQuery(); //rs로 받았음 
		
		if (rs.next()) {
			department = new Department();
			department.setDeptNo(rs.getInt(1));
			department.setDeptName(rs.getString(2));
			department.setFloor(rs.getInt(3));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return department;
	}

	@Override
	public ArrayList<Department> listItems() throws SQLException { //콘솔에서 테이블 리스트 보기용
		ArrayList<Department> list = new ArrayList<>();
		String sql = "select deptno, deptname, floor from department";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while (rs.next()) {
			list.add(new Department(rs.getInt("deptno"), 
					rs.getString("deptname"), 
					rs.getInt("floor")));
		} //arraylist에 담기!!!
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);

		return list;
	}
}
